<?php
require_once( 'AbstractDatasource.php' );

class OracleDatasource extends AbstractDatasource {

	var $oConnection;
	var $sUsername;
	var $sPassword;
	var $sInstance;
	
	function OracleDatasource( $sUsername, $sPassword, $sInstance ) {

		$this->AbstractDatasource();
		$this->sUsername    = $sUsername;
		$this->sPassword    = $sPassword;
		$this->sInstance    = $sInstance;

		$this->_Connect();
	}
	
	function _Connect() {
		$this->SetErrorHandler();
		$this->oConnection = ocilogon( $this->sUsername, $this->sPassword, $this->sInstance );
		$this->oErrorStack->RestoreErrorHandler();
	}
	
	function Disconnect() {
		ocilogoff( $this->oConnection );
		$this->oConnection = null;	
	}

	function IsConnected() {
		return is_resource( $this->oConnection );	
	}

	function _GetResultSet( $sQuery, $aBindVariables = false, $sRetrieveSingleColumnName = false, $bRetrieveSingleRow = false ) {
		if ( !$this->IsConnected() ) {
			return array();	
		}

		$this->SetErrorHandler();
		
		$oStatement = ociparse( $this->oConnection, $sQuery );
		
		if ( is_array( $aBindVariables ) ) {
			foreach( $aBindVariables as $sThisBindName => $sThisBindValue ) {
				ocibindbyname( $oStatement, ':'.$sThisBindName, $sThisBindValue );
			}			
		}
		
		$bSuccess = ociexecute( $oStatement, OCI_DEFAULT );
		
		$aResultSet = array();
		$aDataRow   = array();
		while ( ocifetchinto ( $oStatement, $aDataRow, OCI_ASSOC ) ) {
			if ( $bRetrieveSingleRow ) {
				if ( $sRetrieveSingleColumnName ) {
					$aResultSet = $aDataRow[$sRetrieveSingleColumnName];
				} else {
					$aResultSet = $aDataRow;
				}
			} else {
				if ( $sRetrieveSingleColumnName ) {
					$aResultSet[] = $aDataRow[$sRetrieveSingleColumnName];
				} else {
					$aResultSet[] = $aDataRow;
				}
			}
		}
		
		$this->oErrorStack->RestoreErrorHandler();
		
		return $aResultSet;
	}
	
	
	function GetTableList() {
		
		$sQuery = 'SELECT table_name FROM user_tables';
		return $this->_GetResultSet( $sQuery, false, 'TABLE_NAME' );
	}

	function GetColumnList( $sTableName ) {
		// TODO: Need to ensure that all datatypes are correctly covered for the data precision
		$sQuery = " SELECT column_name AS name
						  , DECODE( data_type
				                  , 'CLOB'  , data_type
				                  , 'BLOB'  , data_type
				                  , 'DATE'  , data_type
								  , 'NUMBER', DECODE( data_scale
				                                    , NULL, 	 DECODE( data_precision
													                   , NULL, data_type
				                                                       , data_type || ' (' || data_precision || ')'
				                                                       )
				                                    ,    0, 	 DECODE( data_precision
													                   , NULL, data_type
				                                                       , data_type || ' (' || data_precision || ')'
				                                                       )
				                                    , data_type || ' (' || data_precision ||',' || data_scale || ')'
				                                    )
				                  , DECODE( data_length
				                          , NULL, data_type
				                          , data_type || ' (' || data_length || ')'
				                          )
				                  ) AS datatype
					 FROM  user_tab_columns
					 WHERE table_name = :TABLE_NAME
					 ORDER BY column_id";
		return $this->_GetResultSet( $sQuery, array( 'TABLE_NAME' => $sTableName ) );
	}
	
	function _GetConstraintColumnList( $sConstraintName ) {

		$sQuery = 'SELECT  column_name
					FROM   user_cons_columns      ucc
					WHERE  ucc.constraint_name = :CONSTRAINT_NAME
					ORDER BY ucc.position';

		return $this->_GetResultSet( $sQuery, array( 'CONSTRAINT_NAME' => $sConstraintName ), 'COLUMN_NAME' );
	}
	
	
	function _IsKeyMandatory( $sKeyName ) {
		
		$sQuery = 'SELECT COUNT(*)            mandatory_field_count
					FROM   user_cons_columns   ucc
					     , user_tab_columns    utc
					WHERE  ucc.constraint_name = :KEY_NAME
					AND    utc.table_name      = ucc.table_name
					AND    utc.column_name     = ucc.column_name
					AND    utc.nullable        = \'N\'';
		
		$iMandatoryFieldCount = $this->_GetResultSet( $sQuery, array( 'KEY_NAME' => $sKeyName ), 'MANDATORY_FIELD_COUNT', true );
		return ( $iMandatoryFieldCount > 0 );
	}
	
	function _GetForeignKeys( $aKeyNames ) {
		$aKeys = array();
		foreach ( $aKeyNames as $aThisKey ) {
			$aKeys[ $aThisKey['NAME'] ] = $this->_GetForeignKey( $aThisKey );
		}
		return $aKeys;
	}
	
	function _GetForeignKey( $aKeyDescription ) {

 		$sQuery =  'SELECT ucc.table_name        to_table
					FROM   user_cons_columns     ucc
					WHERE  ucc.constraint_name = :REFERRING_TO_PK';

		$aResultSet = $this->_GetResultSet( $sQuery, array( 'REFERRING_TO_PK' => $aKeyDescription['REFERRING_TO_PK'] ) );

		$aKey['TO_TABLE']     = $aResultSet[0]['TO_TABLE'];
		$aKey['FROM_TABLE']   = $aKeyDescription['FROM_TABLE'];
		$aKey['FROM_COLUMNS'] = $this->_GetConstraintColumnList( $aKeyDescription['NAME'] );
		$aKey['NAME']         = $aKeyDescription['NAME'];
		$aKey['MANDATORY']    = $this->_IsKeyMandatory( $aKeyDescription['NAME'] );

		return $aKey;
		
	}

	function _GetForeignKeysFrom( $sTableName ) {

 		$sQuery = 'SELECT :TABLE_NAME            from_table
 				        , uc.constraint_name     name
						, uc.r_constraint_name   referring_to_pk
				   FROM   user_constraints       uc
				   WHERE  uc.constraint_type   = \'R\'
				   AND    uc.table_name        = :TABLE_NAME';
 
		return $this->_GetResultSet( $sQuery, array( 'TABLE_NAME' => $sTableName ) );
	}
	
	function _GetForeignKeysTo( $sTableName ) {

 		$sQuery = 'SELECT uc_from.table_name          from_table
 				        , uc_from.constraint_name     name
						, uc_from.r_constraint_name   referring_to_pk
				   FROM   user_constraints            uc_from
				        , user_constraints            uc_to
				   WHERE  uc_from.constraint_type   = \'R\'
				   AND    uc_from.r_constraint_name = uc_to.constraint_name
				   AND    uc_to.table_name          = :TABLE_NAME';
 
		return $this->_GetResultSet( $sQuery, array( 'TABLE_NAME' => $sTableName ) );
	}
	
	function GetForeignKeys( $sTableName ) {
		return $this->_GetForeignKeys( array_merge( $this->_GetForeignKeysTo( $sTableName ), $this->_GetForeignKeysFrom( $sTableName ) ) );
	}

	function GetPrimaryKey( $sTableName ) {

		$sQuery =  'SELECT constraint_name   name
					FROM   user_constraints
					WHERE  table_name      = :TABLE_NAME
					AND    constraint_type = \'P\'';

		$aResultSet = $this->_GetResultSet( $sQuery, array( 'TABLE_NAME' => $sTableName ) );
		$aResultSet[0]['COLUMNS'] = $this->_GetConstraintColumnList( $aResultSet[0]['NAME'] );
		return $aResultSet[0];
	}
	
	function GetDatasourceId() {
		return md5( $this->sUsername . '<<||>>' . $this->sPassword . '<<||>>' . $this->sInstance );	
	}
	
	function GetLastDdl() {
		
		if ( $this->iLastDdl ) {
			return $this->iLastDdl;	
		}
		
		$this->iLastDdl = 0;
		
		// Watch out for Y2K style problems with the duplication of Epoch date in this query.
		$sQuery =  "SELECT ( MAX( last_ddl ) - TO_DATE( '01-01-1970', 'dd-mm-yyyy' ) ) timestamp
					FROM  ( SELECT TO_DATE( timestamp, 'yyyy-mm-dd:hh24:mi:ss' ) last_ddl
							FROM   user_objects
							WHERE  object_type = 'TABLE'
							UNION
							SELECT last_change      last_ddl
							FROM   user_constraints
							)";
		$aResultSet = $this->_GetResultSet( $sQuery );
		if ( isset( $aResultSet[0]['TIMESTAMP'] ) ) {
			$this->iLastDdl = $aResultSet[0]['TIMESTAMP'];
		}
		return $this->iLastDdl;
	}
	
}

?>
